/**
 * 
 */
package com.imonsoft.common.util;

/**
 * 
 * @author LiQianHui
 * @date 2013-04-17
 * @version 2013
 */
public abstract class SqlUtil {

	/**
	 * 
	 * @param table
	 * @param columns
	 *            "columns1,columns2,..."
	 * @return "insert into table (columns1,columns2,...) values (?,?,..)"
	 */
	public static String getInsertSql(String table, Object columns) {
		StringBuilder sb = new StringBuilder();
		sb.append("INSERT INTO ").append(table).append("(");
		if (columns instanceof String) {
			String strColumns = (String) columns;
			sb.append(strColumns.replaceAll(":date", "")).append(")").append(" VALUES (").append(strColumns.replaceAll("[^,:]", "").replaceAll(",", "?,"));
		} else if (columns instanceof Object[]) {
			Object[] arrColumns = (Object[]) columns;
			for (int i = 0; i < arrColumns.length; i++) {
				String column = (String) arrColumns[i];
				sb.append(column.replaceAll(":date", ""));
				if (i < arrColumns.length - 1) {
					sb.append(",");
				}
			}
			sb.append(")").append(" VALUES (");
			for (int i = 0; i < arrColumns.length; i++) {
				String column = (String) arrColumns[i];
				sb.append(column.replaceAll("[^:]", ""));
				if (i < arrColumns.length - 1) {
					sb.append("?,");
				}
			}
		}
		sb.append("?) ");
		return sb.toString().replaceAll(":\\?", "to_date(?,'yyyy-MM-dd HH24:mi:ss')");
	}

	/**
	 * 返回UPDATE...SET...,不带WHERE
	 * 
	 * @param table
	 * @param columns
	 *            "columns1,columns2:date,..."
	 * @return "update table set columns1=?,columns2=to_date(?,'yyyy-MM-dd
	 *         HH24:mi:ss'),... "
	 */
	public static String getUpdateSql(String table, Object columns) {
		StringBuilder sb = new StringBuilder();
		sb.append("UPDATE ").append(table).append(" SET ");
		if (columns instanceof String) {
			String strColumns = (String) columns;
			sb.append(strColumns.replaceAll(",", "=?,"));
		} else if (columns instanceof Object[]) {
			Object[] arrColumns = (Object[]) columns;
			for (int i = 0; i < arrColumns.length; i++) {
				sb.append(arrColumns[i]);
				if (i < arrColumns.length - 1) {
					sb.append("=?,");
				}
			}
		}
		sb.append("=? ");
		return sb.toString().replaceAll(":date=\\?", "=to_date(?,'yyyy-MM-dd HH24:mi:ss')");
	}

	/**
	 * 
	 * @param table
	 * @param columns
	 *            "columns1,columns2:date,..."
	 * @param condition
	 * @return "update table set columns1=?,columns2=to_date(?,'yyyy-MM-dd
	 *         HH24:mi:ss'),... "
	 */
	public static String getUpdateSql(String table, Object columns, String condition) {
		return getUpdateSql(table, columns) + condition;
	}

	/**
	 * 生成Insert语句(带名字参数)
	 * 
	 * @param tableName
	 * @param columnsName
	 * @return
	 */
	public static String createNamedParameterInsertSql(String tableName, String columnsName) {
		columnsName = columnsName.toUpperCase().replace(" ", "");
		StringBuilder sb = new StringBuilder();
		sb.append("INSERT INTO ").append(tableName).append("(").append(columnsName.replaceAll(":DATE", "")).append(") VALUES (");
		String[] columns = columnsName.split(",");
		for (int i = 0; i < columns.length; i++) {
			if (columns[i].endsWith(":DATE")) {
				columns[i] = columns[i].substring(0, columns[i].length() - 5);
				sb.append("to_date(:").append(columns[i]).append(", 'yyyy-MM-dd HH24:mi:ss')");
			} else {
				sb.append(":").append(columns[i]);
			}
			if (i < columns.length - 1) {
				sb.append(",");
			}
		}
		sb.append(")");
		return sb.toString();
	}

	/**
	 * 生成Update语句(带名字参数)
	 * 
	 * @param tableName
	 * @param columnsName
	 * @param condition
	 * @return
	 */
	public static String createNamedParameterUpdateSql(String tableName, String columnsName, String condition) {
		columnsName = columnsName.toUpperCase().replace(" ", "");
		StringBuilder sb = new StringBuilder();
		sb.append("UPDATE ").append(tableName).append(" SET ");
		String[] columns = columnsName.split(",");
		for (int i = 0; i < columns.length; i++) {
			if (columns[i].endsWith(":DATE")) {
				columns[i] = columns[i].substring(0, columns[i].length() - 5);
				sb.append(columns[i]).append("=to_date(:").append(columns[i]).append(", 'yyyy-MM-dd HH24:mi:ss')");
			} else {
				sb.append(columns[i]).append("=:").append(columns[i]);
			}
			if (i < columns.length - 1) {
				sb.append(",");
			}
		}
		if (condition != null) {
			sb.append(" ").append(condition.toUpperCase().trim());
		}
		return sb.toString();
	}

	/**
	 * 生成分页查询的sql语句(Oracle中使用)
	 * 
	 * @param sql
	 * @return 返回分页查询的sql; Oracle:两个"?"依次代表end,start
	 */
	public static String createPageSql(String sql) {
		String pagingSql = "SELECT * FROM (SELECT tt_.*, ROWNUM rownum_ FROM(" + sql + ")tt_ WHERE ROWNUM<=?) WHERE rownum_>=?";
		return pagingSql;
	}

	/**
	 * 生成Oracle分页查询SQL
	 * 
	 * @param sql
	 * @param skip
	 *            [1,2,3,...]
	 * @param max
	 *            [1,2,3,...]
	 * @return
	 */
	public static String createPageSqlOracle(String sql, int skip, int max) {
		int startIndex = 0, endIndex = 0;
		if (skip < 1) {
			startIndex = 1;
		} else {
			startIndex = skip;
		}
		if (max < 1) {
			endIndex = startIndex;
		} else {
			endIndex = startIndex + max - 1;
		}
		String pagingSql = "SELECT * FROM (SELECT tt_.*, ROWNUM rownum_ FROM(" + sql + ")tt_ WHERE ROWNUM<=" + endIndex + ") WHERE rownum_>=" + startIndex;
		return pagingSql;
	}

	/**
	 * 生成Mysql分页查询SQL
	 * 
	 * @param sql
	 * @param skip
	 *            [1,2,3,...]
	 * @param max
	 *            [1,2,3,...]
	 * @return
	 */
	public static String createPageSqlMysql(String sql, int skip, int max) {
		String pagingSql = sql + " LIMIT " + skip + "," + max;
		return pagingSql;
	}

	/**
	 * 生成分页语句(增加START,END参数)
	 * 
	 * @param sql
	 * @return
	 */
	public static String createNamedParameterPageSql(String sql) {
		String pagingSql = "SELECT * FROM (SELECT tt_.*, ROWNUM rownum_ FROM(" + sql + ")tt_ WHERE ROWNUM<=:END) WHERE rownum_>=:START";
		return pagingSql;
	}

	/**
	 * 
	 * @param sql
	 * @return
	 */
	public static String createCountSql(String sql) {
		String countSql = "SELECT COUNT(*) FROM (" + sql + ")";
		return countSql;
	}

	public static void main(String[] args) {
		String sql = getInsertSql("tableName", "columns1:date,c3,columns2:date");
		System.out.println(sql);
		sql = getUpdateSql("tableName", "columns1:date,c3,columns2:date");
		System.out.println(sql);
		sql = getInsertSql("tableName", new Object[] { "columns1:date", "c3", "columns2:date" });
		System.out.println(sql);
		sql = getUpdateSql("tableName", new Object[] { "columns1:date", "c3", "columns2:date" });
		System.out.println(sql);
		sql = getInsertSql("tableName", new String[] { "code", "name", "status", "build_spec", "group_code", "last_change_date:date", "image", "image_path", "eigenvalue", "description" });
		System.out.println(sql);

		String columnsName = "All_No, Cname, Csname, Science_Name, Explain, Ename, Descs, Type_Id, Sex_Sign, Effect_Sign, Update_Date:date, Input_Pycode, Input_Wbcode, Chronic_Sign, Sign, State, Remark";
		System.out.println(createNamedParameterInsertSql("tb_test", columnsName));
		System.out.println(createNamedParameterUpdateSql("tb_test", columnsName, "WHRER 1=1"));

		System.out.println(createPageSqlMysql("select * from tb_demo", 0, 2));
		System.out.println(createPageSqlMysql("select * from tb_demo", 1, 2));
		System.out.println(createPageSqlMysql("select * from tb_demo", 2, 2));
	}
}
